﻿-- OBEJECT: spSecuencia
-- AUTOR:	GMH
CREATE PROC spSequence
	@pnIdLaboratorio	INT,
	@pnIdSucursal		INT,
	@psTabla			VARCHAR(100),
	@psCampoFolio		VARCHAR(50),
	@pnNextValue		BIGINT OUT
AS
BEGIN
	SET NOCOUNT OFF
	
	DECLARE		@sQuery		NVARCHAR(MAX),
				@sNomTabla	NVARCHAR(200)
	SELECT		@sNomTabla = 'seq' + @psTabla + ISNULL('_' + CONVERT(NVARCHAR(10), @pnIdLaboratorio), '') + ISNULL('_' + CONVERT(NVARCHAR(10), @pnIdSucursal),'')
	DECLARE		@tblFolio		TABLE(Folio BIGINT)
	DECLARE		@tblExisteFolio	TABLE(Existe TINYINT)

	SET		@sQuery = '
	IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ''' + @sNomTabla + ''')
	BEGIN
		CREATE TABLE ' + @sNomTabla + '(Folio INT IDENTITY(1,1) PRIMARY KEY, Dummy TINYINT)
	END'
	
	-- SE CREA LA TABLA PARA LA SECUENCIA
	--SELECT @sQuery
	EXEC sp_executesql @sQuery

NEXTVALUE:	
	-- INSERTO EL NEXT VALUE
	SET	@sQuery = 'INSERT INTO ' + @sNomTabla + '(Dummy) VALUES(1); SELECT @@IDENTITY'
	INSERT INTO @tblFolio
	EXEC sp_executesql @sQuery
	
	-- REGRESO EL NEXT VALUE
	SELECT	@pnNextValue = Folio FROM	@tblFolio
	
	-- VERIFICO QUE EL FOLIO NO ESTE EN USO
	SET	@sQuery = 'SELECT 1 FROM ' + @psTabla + ' WHERE ' + @psCampoFolio + '=' + CONVERT(VARCHAR(10), @pnNextValue) +
					CASE WHEN @pnIdLaboratorio IS NULL THEN '' ELSE ' AND IdLaboratorio =' + CONVERT(VARCHAR(10), @pnIdLaboratorio) END + 
					CASE WHEN @pnIdSucursal IS NULL THEN '' ELSE ' AND IdSucursal =' + CONVERT(VARCHAR(10), @pnIdSucursal) END
	DELETE FROM @tblExisteFolio
	INSERT INTO @tblExisteFolio
	EXEC sp_executesql @sQuery
	
	IF  EXISTS (SELECT * FROM @tblExisteFolio)
	BEGIN
		GOTO NEXTVALUE
	END
		
	SET NOCOUNT ON
END